﻿using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class MaintenanceVisitBulk
    {
        public MaintenanceVisitBulk() { }

        public int GetRowNumberMaintenanceVisitApproval(string update_by, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM ( 
                    SELECT * FROM [TBL_MAINTENANCE_VISIT_SPAWEB_TMP] (NOLOCK) WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND [BATCH_ID] = @BATCH_ID AND STATUS = 'P'
                    AND ERR_LINE = 0)AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberMaintenanceVisitByValues(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM( 
                    SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP (NOLOCK) 
                    WHERE BATCH_ID = @BATCH_ID AND IS_DELETED = '0' AND STATUS = 'R' 
                    AND ((CIF LIKE '%' + @PARAM + '%') OR (DEBTOR_NAME LIKE '%' + @PARAM + '%'))) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberMaintenanceVisitReal(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;

            DbCommand cmd;
            sql = @"
                SELECT count(0) AS jmlData FROM( 
                SELECT TP.UNIT_CODE FROM TBL_MAINTENANCE_VISIT_SPAWEB (NOLOCK) T
                RIGHT OUTER JOIN TBL_MAINTENANCE_VISIT_SPAWEB_TMP (NOLOCK) TP
                ON T.CIF = TP.CIF
                WHERE TP.ERR_LINE = 0 AND TP.STATUS = 'A' AND TP.BATCH_ID = @BATCH_ID AND ((TP.CIF LIKE '%' + @PARAM + '%') 
                    OR (TP.DEBTOR_NAME LIKE '%' + @PARAM + '%'))) AS DATA
                ";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberMaintenanceVisitFailed(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData 
                    FROM( 
		                    SELECT * FROM [TBL_MAINTENANCE_VISIT_SPAWEB_TMP] WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND ERR_LINE > 0 AND 
		                    ((CIF LIKE '%' + @PARAM + '%') OR (DEBTOR_NAME LIKE '%' + @PARAM + '%'))
                    ) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<MaintenanceVisitBulkInfo> GetListMaintenanceVisit(string value, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<MaintenanceVisitBulkInfo> list = new List<MaintenanceVisitBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row, * " +
                  "FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP (NOLOCK) WHERE BATCH_ID = @BATCH_ID AND IS_DELETED = '0' AND STATUS = 'R' AND ((CIF LIKE '%' + @PARAM + '%') OR (DEBTOR_NAME LIKE '%' + @PARAM + '%'))" +
                  ")AS LogWithRowNumbers " +
                   "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitBulkInfo
                                        {
                                            VISIT_ID = dr["VISIT_ID"].ToString(),
                                            PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                                            AO_CODE = dr["AO_CODE"].ToString(),
                                            UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                            DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                                            NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                                            DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                                            OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                                            ENTRY_BY = dr["ENTRY_BY"].ToString(),
                                            ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                                            UPDATE_BY = dr["UPDATE_BY"].ToString(),
                                            UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                                            IS_DELETED = dr["IS_DELETED"].ToString(),
                                            UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
                                            UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
                                            IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
                                            RECORD_ID = dr["RECORD_ID"].ToString(),
                                            EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                                            EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                                            EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                                            BATCH_ID = dr["BATCH_ID"].ToString(),
                                            PROOF_CALL = dr["PROOF_CALL"].ToString(),
                                            HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                                            CIF = dr["CIF"].ToString(),
                                            DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                                            EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                                            IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
                                            NIPTeller = dr["NIPTeller"].ToString(),
                                            ACT_TYPE = dr["ACT_TYPE"].ToString(),
                                            ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
                                            ERR_DESC = dr["ERR_DESC"].ToString(),
                                            STATUS = dr["STATUS"].ToString()
                                        });    
                    //(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetInt32(38), dr.GetString(39), dr.GetString(40), dr.GetString(41), "", ""));
                }
                dr.Close();
            }
            return list;
        }

        public List<MaintenanceVisitBulkInfo> GetListMaintenanceVisitReal(string value, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<MaintenanceVisitBulkInfo> list = new List<MaintenanceVisitBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string table = "TP.";
            if (sortBy == "DOWNLOAD_DATE" || sortBy == "ASSIGNED_TO" || sortBy == "IS_ASSIGNED") table = "T.";
            string sql;

            DbCommand cmd;
            sql = "SELECT LogWithRowNumbers.* FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY " + table + sortBy + " " + sortDir + ") AS Row, TP.*" +
                    "FROM TBL_MAINTENANCE_VISIT_SPAWEB (NOLOCK) T " +
                    "RIGHT OUTER JOIN TBL_MAINTENANCE_VISIT_SPAWEB_TMP (NOLOCK) TP " +
                    "ON T.CIF = TP.CIF " +
                    "WHERE TP.ERR_LINE = 0 AND TP.STATUS = 'A' AND TP.BATCH_ID = @BATCH_ID AND ((TP.CIF LIKE '%' + @PARAM + '%') OR (TP.DEBTOR_NAME LIKE '%' + @PARAM + '%'))" +
                  ") AS LogWithRowNumbers " +
                   "WHERE Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitBulkInfo
                                        {
                                            VISIT_ID = dr["VISIT_ID"].ToString(),
                                            PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                                            AO_CODE = dr["AO_CODE"].ToString(),
                                            UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                            DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                                            NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                                            DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                                            OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                                            ENTRY_BY = dr["ENTRY_BY"].ToString(),
                                            ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                                            UPDATE_BY = dr["UPDATE_BY"].ToString(),
                                            UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                                            IS_DELETED = dr["IS_DELETED"].ToString(),
                                            UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
                                            UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
                                            IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
                                            RECORD_ID = dr["RECORD_ID"].ToString(),
                                            EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                                            EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                                            EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                                            BATCH_ID = dr["BATCH_ID"].ToString(),
                                            PROOF_CALL = dr["PROOF_CALL"].ToString(),
                                            HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                                            CIF = dr["CIF"].ToString(),
                                            DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                                            EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                                            IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
                                            NIPTeller = dr["NIPTeller"].ToString(),
                                            ACT_TYPE = dr["ACT_TYPE"].ToString(),
                                            ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
                                            ERR_DESC = dr["ERR_DESC"].ToString(),
                                            STATUS = dr["STATUS"].ToString()
                                        });
                        //(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetDateTime(38), dr.GetString(39), dr.GetInt16(40), dr.GetString(41)));
                }
                dr.Close();
            }
            return list;
        }

        public List<MaintenanceVisitBulkInfo> GetListApprovalMaintenanceVisit(string update_by, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<MaintenanceVisitBulkInfo> list = new List<MaintenanceVisitBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                        "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row, * " +
                        "FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP (NOLOCK) WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND BATCH_ID = @BATCH_ID AND STATUS = 'P' AND ERR_LINE = 0 " +
                ") AS LogWithRowNumbers " +
                   "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitBulkInfo
                                        {
                                            VISIT_ID = dr["VISIT_ID"].ToString(),
                                            PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                                            AO_CODE = dr["AO_CODE"].ToString(),
                                            UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                            DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                                            NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                                            DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                                            OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                                            ENTRY_BY = dr["ENTRY_BY"].ToString(),
                                            ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                                            UPDATE_BY = dr["UPDATE_BY"].ToString(),
                                            UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                                            IS_DELETED = dr["IS_DELETED"].ToString(),
                                            UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
                                            UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
                                            IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
                                            RECORD_ID = dr["RECORD_ID"].ToString(),
                                            EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                                            EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                                            EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                                            BATCH_ID = dr["BATCH_ID"].ToString(),
                                            PROOF_CALL = dr["PROOF_CALL"].ToString(),
                                            HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                                            CIF = dr["CIF"].ToString(),
                                            DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                                            EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                                            IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
                                            NIPTeller = dr["NIPTeller"].ToString(),
                                            ACT_TYPE = dr["ACT_TYPE"].ToString(),
                                            ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
                                            ERR_DESC = dr["ERR_DESC"].ToString(),
                                            STATUS = dr["STATUS"].ToString()
                                        });    
                    //(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetInt32(38), dr.GetString(39), dr.GetString(40), dr.GetString(41)));
                }
                dr.Close();
            }
            return list;
        }

        public List<MaintenanceVisitBulkInfo> GetListMaintenanceVisitFailed(string value, string batch_id, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<MaintenanceVisitBulkInfo> list = new List<MaintenanceVisitBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT  * FROM ( 
                        SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, * FROM( 
                        SELECT [VISIT_ID],[PROSPECT_ID],[AO_CODE],[UNIT_CODE],[DATE_VISIT],[NO_ACCOUNT],[DEBTOR_NAME],[OBJECTIVE_OF_VISIT]
                               ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_DELETED],[UPLOAD_BY],[UPLOAD_DATE],[IS_UPLOAD]
                               ,[RECORD_ID],[EXPIRY_DATE],[EXTEND_DATE],[EXTEND_REASON],[BATCH_ID],[PROOF_CALL],[HASIL_PROOF_CALL],[CIF]
                               ,[KONTAK_LOG],[JENIS_KONTAK],[DATA_TYPE],[EXTEND_DATE_2],[IS_MAIN_DELETED],[NIPTeller],[ACT_TYPE],[ERR_LINE]
                               ,[ERR_DESC],[STATUS]
                FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND ERR_LINE > 0 AND ((CIF LIKE @PARAM) OR (DEBTOR_NAME LIKE @PARAM)))AS DATA 
                    ) AS LogWithRowNumbers 
                    WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitBulkInfo
                                            {
                                                VISIT_ID = dr["VISIT_ID"].ToString(),
                                                PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                                                AO_CODE = dr["AO_CODE"].ToString(),
                                                UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                                DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                                                NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                                                DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                                                OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                                                ENTRY_BY = dr["ENTRY_BY"].ToString(),
                                                ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                                                UPDATE_BY = dr["UPDATE_BY"].ToString(),
                                                UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                                                IS_DELETED = dr["IS_DELETED"].ToString(),
                                                UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
                                                UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
                                                IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
                                                RECORD_ID = dr["RECORD_ID"].ToString(),
                                                EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                                                EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                                                EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                                                BATCH_ID = dr["BATCH_ID"].ToString(),
                                                PROOF_CALL = dr["PROOF_CALL"].ToString(),
                                                HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                                                CIF = dr["CIF"].ToString(),
                                                DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                                                EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                                                IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
                                                NIPTeller = dr["NIPTeller"].ToString(),
                                                ACT_TYPE = dr["ACT_TYPE"].ToString(),
                                                ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
                                                ERR_DESC = dr["ERR_DESC"].ToString(),
                                                STATUS = dr["STATUS"].ToString()
                                            });
                        //(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetDouble(10), dr.GetString(11), dr.GetInt16(12), dr.GetDouble(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetDateTime(28), dr.GetString(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetInt32(34), dr.GetString(35), dr.GetString(36)));
                }
                dr.Close();
            }
            return list;
        }

        //public MaintenanceVisitBulkInfo GetApprovalByID(string CIF, string NO_ACCOUNT)
        //{
        //    MaintenanceVisitBulkInfo ci = new MaintenanceVisitBulkInfo();
        //    Database db = DBConnection.CreateDatabase();
        //    DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP WHERE LTRIM(RTRIM(CIF)) = @CIF AND LTRIM(RTRIM(NO_ACCOUNT)) = @NO_ACCOUNT");
        //    db.AddInParameter(cmd, "@CIF", DbType.String, CIF);
        //    db.AddInParameter(cmd, "@NO_ACCOUNT", DbType.String, NO_ACCOUNT);
        //    cmd.CommandTimeout = 1800;

        //    using (IDataReader dr = db.ExecuteReader(cmd))
        //    {
        //        while (dr.Read())
        //        {
        //            ci = new MaintenanceVisitBulkInfo
        //            {
        //                VISIT_ID = dr["VISIT_ID"].ToString(),
        //                PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
        //                AO_CODE = dr["AO_CODE"].ToString(),
        //                UNIT_CODE = dr["UNIT_CODE"].ToString(),
        //                DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
        //                NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
        //                DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
        //                OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
        //                ENTRY_BY = dr["ENTRY_BY"].ToString(),
        //                ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
        //                UPDATE_BY = dr["UPDATE_BY"].ToString(),
        //                UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
        //                IS_DELETED = dr["IS_DELETED"].ToString(),
        //                UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
        //                UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
        //                IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
        //                RECORD_ID = dr["RECORD_ID"].ToString(),
        //                EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
        //                EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
        //                EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
        //                BATCH_ID = dr["BATCH_ID"].ToString(),
        //                PROOF_CALL = dr["PROOF_CALL"].ToString(),
        //                HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
        //                CIF = dr["CIF"].ToString(),
        //                DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
        //                EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
        //                IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
        //                NIPTeller = dr["NIPTeller"].ToString(),
        //                ACT_TYPE = dr["ACT_TYPE"].ToString(),
        //                ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
        //                ERR_DESC = dr["ERR_DESC"].ToString(),
        //                STATUS = dr["STATUS"].ToString()
        //            };
        //        }
        //        dr.Close();
        //    }
        //    return ci;
        //}

        public MaintenanceVisitBulkInfo GetApprovalByIDandBatchId(string RECORD_ID, string BATCH_ID)
        {
            MaintenanceVisitBulkInfo ci = new MaintenanceVisitBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP WHERE LTRIM(RTRIM(RECORD_ID)) = @RECORD_ID AND LTRIM(RTRIM(BATCH_ID)) = @BATCH_ID");
            db.AddInParameter(cmd, "@RECORD_ID", DbType.String, RECORD_ID);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID);
            cmd.CommandTimeout = 1800;

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new MaintenanceVisitBulkInfo
                                        {
                                            VISIT_ID = dr["VISIT_ID"].ToString(),
                                            PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                                            AO_CODE = dr["AO_CODE"].ToString(),
                                            UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                            DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                                            NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                                            DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                                            OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                                            ENTRY_BY = dr["ENTRY_BY"].ToString(),
                                            ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                                            UPDATE_BY = dr["UPDATE_BY"].ToString(),
                                            UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                                            IS_DELETED = dr["IS_DELETED"].ToString(),
                                            UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
                                            UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
                                            IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
                                            RECORD_ID = dr["RECORD_ID"].ToString(),
                                            EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                                            EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                                            EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                                            BATCH_ID = dr["BATCH_ID"].ToString(),
                                            PROOF_CALL = dr["PROOF_CALL"].ToString(),
                                            HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                                            CIF = dr["CIF"].ToString(),
                                            DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                                            EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                                            IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
                                            NIPTeller = dr["NIPTeller"].ToString(),
                                            ACT_TYPE = dr["ACT_TYPE"].ToString(),
                                            ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
                                            ERR_DESC = dr["ERR_DESC"].ToString(),
                                            STATUS = dr["STATUS"].ToString()
                                        };
                }
                dr.Close();
            }
            return ci;
        }

        public int ApproveBulk(string RECORD_ID, string BATCH_ID, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("MAINTENANCE_VISIT_APPROVAL_BULK");

            db.AddInParameter(cmd, "@RECORD_ID", DbType.String, RECORD_ID);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID);
            cmd.CommandTimeout = 1800;

            return db.ExecuteNonQuery(cmd);
        }

        public Int32[] ApproveBulkAll(string batch_id, string status)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("MAINTENANCE_VISIT_APPROVAL_ALL");

            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@STATUS", DbType.String, status);
            db.AddOutParameter(cmd, "@ROWCOUNT_I", DbType.Int32, 50);
            db.AddOutParameter(cmd, "@ROWCOUNT_U", DbType.Int32, 50);
            db.AddOutParameter(cmd, "@ROWCOUNT_D", DbType.Int32, 50);
            cmd.CommandTimeout = 5600;
            db.ExecuteNonQuery(cmd);
            int insertCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_I"));
            int updateCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_U"));
            int deleteCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_D"));
            Int32[] countArr = new Int32[3] { insertCount, updateCount, deleteCount };
            return countArr;
        }

        public int Reject(string RECORD_ID, string BATCH_ID)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_MAINTENANCE_VISIT_SPAWEB_TMP SET STATUS = 'R', IS_DELETED = '0'
                            WHERE (LTRIM(RTRIM(RECORD_ID)) = @RECORD_ID) 
                                    AND (LTRIM(RTRIM(BATCH_ID)) = @BATCH_ID);");

            db.AddInParameter(cmd, "@RECORD_ID", DbType.String, RECORD_ID);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID);
            cmd.CommandTimeout = 1800;
            return db.ExecuteNonQuery(cmd);
        }

        public MaintenanceVisitBulkInfo Get(string CIF)
        {
            MaintenanceVisitBulkInfo ci = new MaintenanceVisitBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP WHERE LTRIM(RTRIM(CIF)) = @CIF AND IS_DELETED = '0'");
            db.AddInParameter(cmd, "@CIF", DbType.String, CIF);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new MaintenanceVisitBulkInfo
                    {
                        VISIT_ID = dr["VISIT_ID"].ToString(),
                        PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                        AO_CODE = dr["AO_CODE"].ToString(),
                        UNIT_CODE = dr["UNIT_CODE"].ToString(),
                        DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                        NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                        DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                        OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                        ENTRY_BY = dr["ENTRY_BY"].ToString(),
                        ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                        UPDATE_BY = dr["UPDATE_BY"].ToString(),
                        UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                        IS_DELETED = dr["IS_DELETED"].ToString(),
                        UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
                        UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
                        IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
                        RECORD_ID = dr["RECORD_ID"].ToString(),
                        EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                        EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                        EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                        BATCH_ID = dr["BATCH_ID"].ToString(),
                        PROOF_CALL = dr["PROOF_CALL"].ToString(),
                        HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                        CIF = dr["CIF"].ToString(),
                        DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                        EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                        IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
                        NIPTeller = dr["NIPTeller"].ToString(),
                        ACT_TYPE = dr["ACT_TYPE"].ToString(),
                        ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
                        ERR_DESC = dr["ERR_DESC"].ToString(),
                        STATUS = dr["STATUS"].ToString()
                    };
                    //(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetInt32(37), dr.GetString(38), dr.GetString(39), dr.GetString(40));
                }
                dr.Close();
            }
            return ci;
        }

        public void delete(string batch_id)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_MAINTENANCE_VISIT_SPAWEB_TMP SET IS_DELETED = '1' WHERE BATCH_ID = @BATCH_ID");
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.ExecuteNonQuery(cmd);
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public int deteleOnBulk(MaintenanceVisitBulkInfo Info)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_MAINTENANCE_VISIT_SPAWEB_TMP SET IS_DELETED = '1', STATUS = 'P', UPDATE_BY = @UPDATE_BY, UPDATE_DATE = @UPDATE_DATE, ACT_TYPE = @ACT_TYPE
                                WHERE LTRIM(RTRIM(CIF)) = @CIF");

                db.AddInParameter(cmd, "@CIF", DbType.String, Info.CIF);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Info.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Info.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, Info.ACT_TYPE);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception e) { }
            return status;
        }

        public List<MaintenanceVisitBulkInfo> GetListMaintenanceVisitRealExcel(string value, string batch_id)
        {
            List<MaintenanceVisitBulkInfo> list = new List<MaintenanceVisitBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;

            DbCommand cmd;
            sql = "SELECT LogWithRowNumbers.* FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY TP.[UPDATE_DATE] ASC) AS Row, TP.* " +
                        "FROM TBL_MAINTENANCE_VISIT_SPAWEB_TMP TP " +
                " LEFT OUTER JOIN TBL_MAINTENANCE_VISIT_SPAWEB T " +
                " ON T.CIF = TP.CIF " +
                " WHERE TP.ERR_LINE = 0 AND TP.STATUS = 'A' AND TP.BATCH_ID = @BATCH_ID AND ((TP.CIF LIKE '%' + @PARAM + '%') OR (TP.DEBTOR_NAME LIKE '%' + @PARAM + '%'))" +
                  ") AS LogWithRowNumbers ";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MaintenanceVisitBulkInfo
                    {
                        VISIT_ID = dr["VISIT_ID"].ToString(),
                        PROSPECT_ID = dr["PROSPECT_ID"].ToString(),
                        AO_CODE = dr["AO_CODE"].ToString(),
                        UNIT_CODE = dr["UNIT_CODE"].ToString(),
                        DATE_VISIT = DateTime.Parse(dr["DATE_VISIT"].ToString()),
                        NO_ACCOUNT = dr["NO_ACCOUNT"].ToString(),
                        DEBTOR_NAME = dr["DEBTOR_NAME"].ToString(),
                        OBJECTIVE_OF_VISIT = dr["OBJECTIVE_OF_VISIT"].ToString(),
                        ENTRY_BY = dr["ENTRY_BY"].ToString(),
                        ENTRY_DATE = DateTime.Parse(dr["ENTRY_DATE"].ToString()),
                        UPDATE_BY = dr["UPDATE_BY"].ToString(),
                        UPDATE_DATE = DateTime.Parse(dr["UPDATE_DATE"].ToString()),
                        IS_DELETED = dr["IS_DELETED"].ToString(),
                        UPLOAD_BY = dr["UPLOAD_BY"].ToString(),
                        UPLOAD_DATE = DateTime.Parse(dr["UPLOAD_DATE"].ToString()),
                        IS_UPLOAD = dr["IS_UPLOAD"].ToString(),
                        RECORD_ID = dr["RECORD_ID"].ToString(),
                        EXPIRY_DATE = DateTime.Parse(dr["EXPIRY_DATE"].ToString()),
                        EXTEND_DATE = DateTime.Parse(dr["EXTEND_DATE"].ToString()),
                        EXTEND_REASON = dr["EXTEND_REASON"].ToString(),
                        BATCH_ID = dr["BATCH_ID"].ToString(),
                        PROOF_CALL = dr["PROOF_CALL"].ToString(),
                        HASIL_PROOF_CALL = dr["HASIL_PROOF_CALL"].ToString(),
                        CIF = dr["CIF"].ToString(),
                        DATA_TYPE = dr["DATA_TYPE"].ToString(), //FUNDING LENDING
                        EXTEND_DATE_2 = DateTime.Parse(dr["EXTEND_DATE_2"].ToString()),
                        IS_MAIN_DELETED = dr["IS_MAIN_DELETED"].ToString(),
                        NIPTeller = dr["NIPTeller"].ToString(),
                        ACT_TYPE = dr["ACT_TYPE"].ToString(),
                        ERR_LINE = int.Parse(dr["ERR_LINE"].ToString()),
                        ERR_DESC = dr["ERR_DESC"].ToString(),
                        STATUS = dr["STATUS"].ToString()
                    });                        //(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetDateTime(38), dr.GetString(39), dr.GetInt16(40), dr.GetString(41), dr.GetString(42), dr.GetString(43), dr.GetString(44)));
                }
                dr.Close();
            }
            return list;
        }
        //        public List<MaintenanceVisitBulkInfo> GetListMaintenanceVisit(string value, string batch_id)
        //        {
        //            List<MaintenanceVisitBulkInfo> list = new List<MaintenanceVisitBulkInfo>();
        //            Database db = DBConnection.CreateDatabase();
        //            string sql;
        //            DbCommand cmd;
        //            sql = @"SELECT TMP.[BATCH_ID],TMP.[CIF],TMP.[LOAN_NUMBER]
        //                          ,TMP.[UNIT_CODE],TBL_MS_BRANCH.BRANCH_NAME, TBL_MS_CLUSTER.CLUSTER_NAME
        //	                      ,TBL_MS_REGIONAL.REGIONAL_NAME,TMP.[DEBITOR_NAME],TMP.[PRODUCT_CODE]
        //                          ,TMP.[CURRENT_OS],TMP.[CURRENT_INSTALLMENT],TMP.[OFFER_PLAFON]
        //                          ,TMP.[OFFER_INSTALLMENT],TMP.[OFFER_INTEREST],TMP.[OFFER_TENOR]
        //                          ,TMP.[OFFER_FRESH_MONEY],TMP.[PRIORITY],TMP.[JENIS_USAHA]
        //                          ,TMP.[KODYA_KAB_BUSINESS],TMP.[KECAMATAN_BUSINESS],TMP.[KELURAHAN_BUSINESS]
        //                          ,TMP.[ZIP_CODE_BUSINESS],TMP.[ADDRESS_BUSINESS],TMP.[MOBILE_PHONE]
        //                          ,TMP.[OFFICE_PHONE],TMP.[RESIDENTIAL_PHONE],TMP.[DATE_VISIT_PLAN]
        //                          ,TMP.[ENTRY_BY],TMP.[ENTRY_DATE],TMP.[UPDATE_BY]
        //                          ,TMP.[UPDATE_DATE],TMP.[PROSPECT_TYPE],TMP.[DEBTOR_TYPE]
        //                          ,TMP.[CHANNELING],TMP.[EKSTERNAL],TMP.[REFERENSI]
        //                          ,TMP.[ERR_LINE],TMP.[ERR_DESC],TMP.[ACT_TYPE]
        //                    FROM [TBL_TOPUP_TMP] TMP LEFT JOIN 
        //	                    TBL_MS_BRANCH ON TMP.UNIT_CODE = TBL_MS_BRANCH.BRANCH_ID LEFT JOIN
        //                        TBL_MS_CLUSTER_BRANCH ON TBL_MS_BRANCH.BRANCH_ID = TBL_MS_CLUSTER_BRANCH.BRANCH_ID LEFT JOIN
        //                        TBL_MS_CLUSTER ON TBL_MS_CLUSTER_BRANCH.CLUSTER_ID = TBL_MS_CLUSTER.CLUSTER_ID LEFT JOIN
        //                        TBL_MS_REGION_CLUSTER ON TBL_MS_CLUSTER.CLUSTER_ID = TBL_MS_REGION_CLUSTER.CLUSTER_ID LEFT JOIN
        //                        TBL_MS_REGIONAL ON TBL_MS_REGION_CLUSTER.REGIONAL_ID = TBL_MS_REGIONAL.REGIONAL_ID
        //                   WHERE BATCH_ID = @BATCH_ID AND STATUS = 'R' 
        //                   AND IS_DELETED = '0' AND ((CIF LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%'))";
        //            cmd = db.GetSqlStringCommand(sql);
        //            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
        //            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
        //            cmd.CommandTimeout = 1800;
        //            using (IDataReader dr = db.ExecuteReader(cmd))
        //            {
        //                while (dr.Read())
        //                {
        //                    list.Add(new MaintenanceVisitBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetDouble(9), dr.GetDouble(10), dr.GetDouble(11), dr.GetDouble(12), dr.GetString(13), dr.GetInt16(14), dr.GetDouble(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetString(23), dr.GetString(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetDateTime(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetInt32(36), dr.GetString(37), dr.GetString(38)));
        //                }
        //                dr.Close();
        //            }
        //            return list;
        //        }
    }
}